【レポート】Redshift管理者必見！クエリ同時実行数問題を解決するWLMとConcurrency Scalingをフル活用したコーセーの事例 #AWSSummit

DA事業本部の春田です。

AWS Summit Online絶賛開催中！ということで、本記事では「CUS-63: SAP BusinessObjects向けクエリパフォーマンス向上の取り組みと、Amazon Redshift ConcurrencyScalingの活用による効果」の内容についてまとめていきます。

セッション情報

• 株式会社コーセー 情報統括部 小林 裕也 氏

BIツールである SAP BusinessObjectsは、多様な利用者が存在し実行されるクエリはユーザごとに異なります。利用者増大に伴いクエリパフォーマンス問題を長年抱えていましたが、その中で行った改善活動と、ConcurrencyScalingの登場・活用による効果についてご紹介します。

※セッション動画は以下リンク

アジェンダ

1. 会社紹介
2. コーセーの分析基盤
3. パフォーマンス向上への取り組み
4. Concurrency Scaling活用による効果
5. 結論

会社紹介

• 株式会社コーセー
• 創業: 1946年3月2日
• 従業員数: 7,758名（連結）
• 事業内容: 化粧品の製造、販売
• 売上高: 3,329億万円（19年3月期）
• 営業利益: 524億円（営業利益率 15.7%）
• 本社: 東京都中央区日本橋
• 営業所: 全国39支店
• 研究所: 東京都北区・板橋区
• 工場: 埼玉県・群馬県
• 研修センター: 東京都北区王子
• 関係会社: コーセーコスメポート、アルビオン他

• コーセーのブランド戦略

  • 3軸のブランド戦略
    • 専門店・百貨店を中心に展開している高級な「HIGH-PRESTIGE」
    • 量販店・ドラッグストアなど、幅広い流通チャネルに対応している「PRESTIGE」
    • シャンプーなどのトイレタリー製品を含めた化粧品を、量販店・ドラッグストア・コンビニなどのチャネルで展開している「COSMETARIES」
  • 2タイプのブランド名
    • コーセーの名前を前面に押し出した「コーセーブランド」
      • 例） INFINITY、雪肌精、ONE BY KOSE、ESPRIQUE、VISEE
    • あえてコーセーの名前を出さず、ブランド独自の世界観を重視した「インディビジュアルブランド」
      • 例） COSME DECORTE、JILLSTUART、ADDICTION、Predia

コーセーの分析基盤

• 分析基盤が構築される前の背景・課題
  • 部門や業務ごとに個別最適化された統合分析基盤（サイロ化データの統合）データウェアハウスを構築
    • 複数の情報系システムが存在し管理費が増大
    • システム毎に利用方法が異なるため利用者に負荷
    • マーケティングデータ分析ニーズに対応できない
    • 集計ロジック分散によるシステム毎の結果の差異
• 解決
  • 統合分析基盤を構築（サイロ化データの統合）
  • 意思決定に必要な情報を一元管理
• 効果
  • データベース統合によりデータ抽出効率が向上
    • 情報の一元管理
    • システム操作方法の統一化
  • iPadの活用
    • 店頭提案力・説得力の強化
    • 隙間時間の有効活用（時間短縮）
  • 必要なデータの自由分析を実現
    • 社内のデータ活用推進（教育啓蒙）
      • 初級者:欲しいデータが簡単に（定型）
      • 上級者:より高度な分析（応用）
    • 活用スキルの統一化（ナレッジの蓄積）
  • BIツールの最新機能を利用
    • 分析機能、表現力（グラフ）の強化
    • 成功事例の共有化
  • クラウド（AWS）利用により負荷変動に柔軟
    • 既存システムの老朽化対策
    • 導入コスト、運用コストの削減

• 統合分析システムの構成
  • 社内のシステムからEC2インスタンス上のETLツールでデータを収集
  • S3経由でRedshiftへロード
  • BIツールのBusinessObjectsでRedshiftのテーブルを参照
  • 分析レポートをiPadや社内PCから閲覧
• システム利用状況 ※（）は公開時
  • 利用ユーザ数: 約1500人（約1000人）
  • ピーク時の同時利用数: 平均100セッション（平均15セッション）
  • 1日あたりの利用者数: 平均400人（平均50人）
  • 休日を除く1日あたりの検索数: 平均2800検索（平均700検索）

• 利用者が増えることで抱えた問題……
  • 想定していたパフォーマンスがでない
  • 複数人が同時実行しただけでレスポンスが悪化
  • ピーク時の検索100人くらいで、通常の数十倍時間がかかるケースもある
  • 単一の検索では28秒程度
  • 同時検索が増えるにつれレスポンス悪化

• ボトルネックとなっていた部分
  • ログを検証した結果、レポートが表示されるまでの時間の内、90%がRedshiftのクエリ処理時間であることが判明
  • クエリを分析すると、20%のクエリが処理待ち状態となっていた
  • 通常数秒程度で結果が出力されるクエリも数分程度の時間を要していた

→ Redshiftは同時実行に弱いことが判明

パフォーマンス向上への取り組み

• それまでのRedshiftの設定
  • Redshift (dc1.large ノード数: 10) ※ノードタイプは後にdc2.largeに変更
  • WLMは3つのキューによって構成
    • 基本的にはデフォルトの設定
    • 検索用（メモリ割当: 70%、並列数: 5）
    • バッチ処理用（メモリ割当: 20%、並列数: 2）
    • 管理者用（メモリ割当: 10%、並列数: 1）
  • テーブル構造
    • 圧縮タイプはデフォルト
    • マスタはALL分散、トランザクションはEVEN分散

取り組み1 - WLMの設定変更

• WLM（Work Load Management）
  • クエリに対して割り当てるメモリの割合や並列度、タイムアウトの時間を指定
  • リソースの非効率な配分や長時間実行されるクエリを止めて、クラスタリソースを無駄遣いしないようにする
  • デフォルトは5
  • 並列数は最大で50まで設定できるようだが、公式のベストプラクティスでは、キュー全体で15以下が推奨
  • 検証として、WLMの検索用キュー並列処理数を2、5、12で30同時実行し、レスポンス測定

→ 結果（×） …顕著な改善は見られなかった

• 取り組みの副産物として分かったこと
  • 設定数を減らす …1クエリあたりのメモリ割当量が増え、単体のク エリパフォーマンスは向上
  • 設定数を増やす …同時に実行できるクエリは増えるが、メモリ割当量は減るため、1クエリあたりのパフォーマンスは低下
  • 改善は見られなかったものの、処理性能 < 並列処理数を重視
  • 検索用キューのクエリ並列処理数は12で設定

取り組み2 - 圧縮タイプの変更

• Redshiftは列レベルで圧縮形式を選ぶことができる
  • 元々は標準の圧縮タイプを利用 → 一部圧縮効率が悪いものも
• 適切な形式を選択することで以下のような効果を発揮
  • ストレージスペースが節約
  • データのサイズが軽量化 → ディスクI/Oの量が減少
  • クエリパフォーマンスが向上

→ 結果（〇） …圧縮タイプを再設定することで、ディスクIOが削減

クエリパフォーマンスは大きく改善したが、並列処理数にはアプローチできていない

取り組み3 - カラムサイズの変更

• Redshiftは列データを非常に効果的に圧縮する
  • 必要なサイズよりかなり大きなカラムを作成しても、テーブルのサイズにはあまり影響しない
  • UTF-8を想定して、日本語用に3倍のカラムのサイズを確保
• 複雑なクエリの処理中に、中間クエリ結果を一時テーブルに格納される場合がある
  • 一時テーブルは圧縮されないので、不必要に大きなカラムがメモリやディスク領域を過剰消費
  • その結果、クエリパフォーマンスに影響を与える可能性がある
• 全体的にカラムサイズを見直し、縮小して、クエリを実行

→ 結果（×） …顕著な改善は見られなかった

取り組み4 - ソートキーの変更

• 頻繁に使用される特定のカラムに対して、ソートキーを設定し、ディスクアクセスの範囲を最小にする
• ソートキーの注意点
  • テーブルにソートキーを付けても自動でソートはされない
  • 自動ソートされるのは、空のテーブルに一括ロードした場合のみ
  • 追加データをロード、INSERTをした場合、VACUUMコマンドが必用
• 高頻度で利用される「年月日」カラムをソートキーとして設定、処理時間を検証

→ 結果（×） …顕著な改善は見られなかった

取り組み5 - RDSの導入

• 様々な取り組みを行ったが、同時処理能力を根本的に解決することはできなかった
  • → リソース増強に踏み切る → RDSの導入
• 同時処理能力が求められる機能で利用するテーブルをRDSに逃がし、Redshiftの負荷を減らす
  • RDSはPostgreSQLを採用
    • 同時検索に強い
    • RedshiftとSQLの親和性が高い
    • RedshiftとのDBリンクが可能

→ 結果（◎） …並列処理に強いデータベースであったことから、同時検索時のパフォーマンスは劇的に改善

• 統合分析システムの構成 ※RDS導入後

取り組みの結果

• Redshift側でのパラメータ調整ではシングル実行での改善は見込めるが、同時接続問題への根本的な解決はしなかった
• RDS導入は大きな効果があった
  • データベース周りの負荷は分散され、システムは一定の安定稼働をむかえることができた

そして月日は流れ、2019年3月30日、「Concurrency Scaling」が登場

Concurrency Scaling活用による効果

• Concurrency Scalingとは
  • 同じ性能を持つクラスターが複数追加され、増えた分だけ同時実行数が倍々で増える機能
• 価格
  • 各追加クラスターでクエリが実行された期間（秒数）に対して課金
  • 1日1時間分は無料
  • 最大で30時間分をクレジット可能

Concurrency Scaling効果検証1

• Concurrency Scalingを適用する手順
  • ワークロード管理のパラメータより、Concurrency Scalingの最大追加クラスター数を5に変更
    • デフォルトは1、最大は10
  • ワークロード管理から、Concurrency Scalingを適用したいキューのConcurrency Scaling modeをautoに変更
    • デフォルトはoff

• 効果検証
  • 1か月間システムを通常稼働し、効果を測定
  • 結果、設定するだけで約20%のパフォーマンス改善を確認
  • Concurrency Scalingの利用状況を確認したところ、実はそこまで追加クラスターがアタッチされていなかった

→ Concurrency Scalingを十分に活用するためには、適切な設定を行うことが必要！

• Concurrency Scalingの利用時間とクエリを再度分析し、以下の傾向を発見
  • クエリ実行時間が10秒以上かかるものが全体の20%弱を占める
  • 1分を超えるロングランクエリが比較的多い
  • クラスターがアタッチされる際は、60秒以上のキュー待ちクエリが発生している
• スロットあたりのメモリ割当量が低いため、クエリ処理に時間がかかりロングランクエリが発生？
  • WLMの設定で、クエリ並列処理数を12に設定していた
• 追加クラスターがアタッチされるには60秒程度のキュー待ちが必要？

→ スロット数を少なくし、クエリあたりのメモリ割当を増やす
→ 頻繁にキュー待ちが発生し、Concurrency Scalingを有効的に利用できるのでは？

Concurrency Scaling効果検証2

• スロット数をそれぞれ5、4、3に変更し、1か月間ずつ稼働して検証
  • スロット数を減らし1クエリのメモリ割当を増やすことで、40%程度同時実行パフォーマンスが改善

• クエリ構成比の変化
  • スロットを減らすことで、5秒以下レンジのクエリ割合が増加
  • 5〜20秒レンジのクエリが減少しているためパフォーマンスは向上

• Concurrency Scaling利用状況の確認
  • Concurrency Scaling Activity
    • スロット数を5以下に設定することで、追加クラスターはコンスタントにアタッチ
    • 最大で4程度 ※通常時は1~2程度
  • Concurrency Scaling Usage in Minutes (Sum)のグラフ
    • スロット5から、スロット4・スロット3になるにつれて、グラフの面積が増加
    • CS利用時間はスロット数に依存（スロット数が少ないほど利用時間↑）

WLMのクエリ並列処理数はデフォルトが5なので、Concurrency Scalingを適用するときは、こちらの値も調節する必要がある！

• CSを最大限に利用する設定
  • スロット3で1日あたり67分の利用に収まり、無料で最大限活用
    • 利用しない土日分のクレジットも加味
  • スロット2 = グラフの上がり幅から予測
    • 無料枠を超過する予測

結論

• Concurrency Scalingを利用だけで、クエリパフォーマンスは向上 ※当社環境
• スロット数を少なくしたほうがConcurrency Scalingの利用率が上昇（40%程度まで向上）
  • キューイング割合が増加することで、追加クラスターがアタッチされやすくなる
  • 即時利用可能になる時間が増加
  • 結果としてパフォーマンスが向上
• 最もConcurrency Scalingを有効活用できる設定はスロット3
  • 休日はConcurrency Scalingが利用されることがないことを考慮しても、無料枠を最大限活用できている
  • スロット2は無料クレジット枠を超過する予想
• Concurrency Scalingを利用することで同時実行処理数が飛躍的に向上
• ロングランクエリの割合が多い場合は恩恵を感じやすい
• 無謀な設定を行わない限り、無料クレジット枠で利用が可能
• Concurrency Scalingを最大限に活用するには、自社のクエリ傾向を分析し調整を行うことが重要